﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Script.Services;
using DAO;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using System.Web.Script.Serialization;


/// <summary>
/// Summary description for Language
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ScriptService]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Language : System.Web.Services.WebService
{

    public Language()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }
    /// <summary>
    /// Load all languages
    /// </summary>
    /// <param name="page"></param>
    /// <param name="pageSize"></param>
    /// <param name="sidx"></param>
    /// <param name="sord"></param>
    /// <param name="letter"></param>
    /// <returns></returns>
    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml)]
    public XmlDocument LoadAll(int page, int pageSize, string sidx, string sord,string letter)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            string q = "";
            if (letter == "") q = "SELECT count(id) as countid FROM dbo.Fn_GetAllLanguages(0, 1000000, 'id', 'asc')";
            else q = "SELECT count(id) as countid FROM dbo.Fn_GetAllLanguages(0, 100000, 'id', 'asc')"
                + " WHERE UPPER(SUBSTRING(language_name, 1, 1)) = '" + letter.ToUpper() + "'";
            using (SqlCommand cmd = new SqlCommand(q, conn))
            {
                SqlDataReader dr = null;
                int start = page * pageSize - pageSize;
                if (start < 0) start = 0;
                int limit = pageSize * page;

                StringBuilder str = new StringBuilder();
                SqlParameter[] pars = new SqlParameter[4];
                pars[0] = new SqlParameter("start", start);
                pars[1] = new SqlParameter("limit", limit);
                pars[2] = new SqlParameter("order", sidx);
                pars[3] = new SqlParameter("orderdir", sord);
                int total = 0;
                dr = cmd.ExecuteReader();
                if (dr != null)
                {
                    if (dr.HasRows)
                    {
                        if (dr.Read()) Int32.TryParse(dr["countid"].ToString(), out total);
                    }
                }
                dr.Close();
                if (letter == "") cmd.CommandText = "SELECT * FROM dbo.Fn_GetAllLanguages(@start, @limit, @order, @orderdir) ORDER By " + sidx + " " + sord;
                else cmd.CommandText = "SELECT * FROM dbo.Fn_GetAllLanguages(@start, @limit, @order, @orderdir)"
                    + " WHERE UPPER(SUBSTRING(language_name, 1, 1)) = '" + letter.ToUpper() + "' ORDER By " + sidx + " " + sord;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                if (dr1 != null)
                {
                    if (dr1.HasRows)
                    {
                        str.Append("" +
                                "<rows><page>" + page + "</page>" +
                                "<total>" + Math.Ceiling((double)total / pageSize).ToString() + "</total><records>" + total + "</records>");
                        while (dr1.Read())
                        {
                            str.Append("<row id='" + dr1["id"] + "'>" +
                                "<cell>" + dr1["id"] + "</cell>" +
                                "<cell>" + dr1["language_name"] + "</cell>" +
                                "<cell>" + dr1["short"] + "</cell>" +
                                "<cell>" + dr1["isActive"] + "</cell>" +
                                "</row>");
                        }

                        str.Append("</rows>");
                    }
                }

                if (dr1 != null && !dr1.IsClosed) dr1.Close();
                if (str.Length != 0)
                {
                    XmlDocument d = new XmlDocument();
                    d.LoadXml(str.ToString());
                    return d;
                }
                else return null;
            }
        }
    }

    /// <summary>
    /// language object for serialization
    /// </summary>
    public class LanguageObj
    {
        public long _id { get; set; }
        public string _language_name { get; set; }
        public string _language_short { get; set; }
        public bool _isActive { get; set; }
    }
    /// <summary>
    /// get language by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string getLang(long id)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SELECT * from dbo.Fn_GetLanguage(@id)", conn))
            {
                StringBuilder str = new StringBuilder();
                SqlParameter[] pars = new SqlParameter[1];
                pars[0] = new SqlParameter("id", id);

                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                SqlDataReader dr1 = cmd.ExecuteReader();
                LanguageObj l = new LanguageObj();
                if (dr1.Read())
                {
                    long _id = 0;
                    string lan_name = "";
                    string lan_short = "";
                    bool isActive = false;
                    Int64.TryParse(dr1["id"].ToString(), out _id);
                    lan_name = dr1["language_name"].ToString();
                    bool.TryParse(dr1["isActive"].ToString(), out isActive);
                    lan_short = dr1["short"].ToString();
                    l._id = _id;
                    l._language_name = lan_name;
                    l._isActive = isActive;
                    l._language_short = lan_short;
                }

                if (dr1 != null && !dr1.IsClosed) dr1.Close();

                List<LanguageObj> lst = new List<LanguageObj>();
                lst.Add(l);
                JavaScriptSerializer js = new JavaScriptSerializer();
                string strJSON = js.Serialize(l);
                return strJSON;
            }
        }
    }

    /// <summary>
    /// update language
    /// </summary>
    /// <param name="id"></param>
    /// <param name="language_name"></param>
    /// <param name="isActive"></param>
    /// <returns></returns>
    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string updateLang(long id, string language_name, bool isActive, string _short)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_UpdateLanguage", conn))
            {
                SqlParameter[] pars = new SqlParameter[4];
                pars[0] = new SqlParameter("id", id);
                pars[1] = new SqlParameter("language_name", language_name);
                pars[2] = new SqlParameter("isActive", isActive);
                pars[3] = new SqlParameter("short", _short);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                JavaScriptSerializer js = new JavaScriptSerializer();

                return (val > 0) ? js.Serialize("true") : js.Serialize("false");
            }
        }
    }

    /// <summary>
    /// insert new language
    /// </summary>
    /// <param name="language_name"></param>
    /// <param name="isActive"></param>
    /// <returns></returns>
    [WebMethod(Description = "")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string insLang(string language_name, bool isActive, string _short)
    {
        using (SqlConnection conn = new SqlConnection(DAO.Connection.CONN_STRING1))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand("SP_InsLanguage", conn))
            {
                SqlParameter[] pars = new SqlParameter[3];
                pars[0] = new SqlParameter("language_name", language_name);
                pars[1] = new SqlParameter("isActive", isActive);
                pars[2] = new SqlParameter("short", _short);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                foreach (SqlParameter s in pars)
                    cmd.Parameters.Add(s);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                JavaScriptSerializer js = new JavaScriptSerializer();

                return (val > 0) ? js.Serialize("true") : js.Serialize("false");
            }
        }
    }

}

